﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using WF_TheTourManagement.EntityClass;

namespace WF_TheTourManagement
{
    public struct strLocation
    {
        int LocationId { get; set; }
        string LocationName { get; set; }
        string LocationDescription { get; set; }

    }

    public class Location
    {
        int LocationId { get; set; }
        string LocationName { get; set; }
        string LocationDescription { get; set; }

        public Location() { }

        public Location(Location _location) {
            this.LocationId = _location.LocationId;
            this.LocationName = _location.LocationName;
            this.LocationDescription = _location.LocationDescription;
        
        }

        public Location(string LocationName, string LocationDescription)
        {
            this.LocationDescription = LocationDescription;
            this.LocationName = LocationName;
        }

        public Location(int LocationId, string LocationName, string LocationDescription)
        {
            this.LocationId = LocationId;
            this.LocationDescription = LocationDescription;
            this.LocationName = LocationName;
        }

        public DataTable getLocationsList()
        {
            List<Location> lList = new List<Location>();

            DataTable dataTable = new DataTable("Locations");
            SqlDataAdapter dataAdapter = new SqlDataAdapter();

            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = DataConnection.Connection();

            //Cach 1: dung cau van tru tiep
            //sqlCmd.CommandType = CommandType.Text;
            //sqlCmd.CommandText = @"select * from names";
            //da.SelectCommand = sqlCmd;
            //da.Fill(dt);

            //Cach 2: thuc hien thong qua Stored Procedure
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "getAllLocations";
            SqlDataReader daReader = sqlCmd.ExecuteReader();
            dataTable.Load(daReader);  

            return dataTable;
            DataConnection.closeConnection();
        }

        public void delete()
        {

        }

        public void saveLocation(DataTable dt)
        {
            DataRow row = dt.NewRow();
            row["LocationName"] = LocationName;
            row["LocationDescription"] = LocationDescription;
            dt.Rows.Add(row);

            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmdInsert = new SqlCommand();
            cmdInsert.Connection = DataConnection.Connection();
            cmdInsert.CommandType = CommandType.Text;
            cmdInsert.CommandText = @"Insert into Locations (LocationName, LocationDescription)
                                                    values  (@LocationName, @LocationDescription)";
            cmdInsert.Parameters.Add("@LocationName", SqlDbType.NVarChar, 50, "LocationName");
            cmdInsert.Parameters.Add("@LocationDescription", SqlDbType.NVarChar, 50, "LocationDescription");

            da.InsertCommand = cmdInsert;
            da.Update(dt);
        }

        public void updateLocation(DataTable dt)
        {
            DataRow row= dt.Select("LocationId = " + LocationId)[0];
            
            row.BeginEdit();
            row["LocationName"] = LocationName;
            row["LocationDescription"] = LocationDescription;

            row.EndEdit();
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmdUpdate = new SqlCommand();
            cmdUpdate.Connection = DataConnection.Connection();
            cmdUpdate.CommandType = CommandType.Text;
            cmdUpdate.CommandText = @"update Locations set LocationName = @LocationName, 
                                                            LocationDescription = @LocationDescription
                                                            where LocationId = @LocationId";
            cmdUpdate.Parameters.Add("@LocationName", SqlDbType.NVarChar, 50, "LocationName");
            cmdUpdate.Parameters.Add("@LocationDescription", SqlDbType.Text, 50, "LocationDescription");
            cmdUpdate.Parameters.Add("@LocationId", SqlDbType.Int, 50, "LocationId");
            da.UpdateCommand = cmdUpdate;
            da.Update(dt);
        }

        public void deleteLocation(DataTable dt)
        {
            DataRow row = dt.Select("LocationId = " + LocationId)[0];

            row.BeginEdit();
            row.Delete();
            row.EndEdit();

            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmdDelete = new SqlCommand();
            cmdDelete.Connection = DataConnection.Connection();
            cmdDelete.CommandType = CommandType.Text;
            cmdDelete.CommandText = @"delete from Locations where LocationId = @LocationId";            
            cmdDelete.Parameters.Add("@LocationId", SqlDbType.Int, 50, "LocationId");
            da.DeleteCommand = cmdDelete;
            da.Update(dt);
        }

        public void searchLocations()
        {

        }

        public DataTable allLocations()
        {
            DataTable dt = new DataTable();
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = DataConnection.Connection();

            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "getAllLocations";
            SqlDataReader daReader = sqlCmd.ExecuteReader();
            dt.Load(daReader);

            return dt;
        }

    }
}
